


ALTER         function fn_hold_lot(@product_id int, @lot_number varchar(50))
 returns @fn_results Table(Lot_ID int,
 		 ProcessStep_ID bigint,
 		 Location_ID int,
 		 PS_Type_Name varchar(20),
 		 Lot_Number varchar(50),
 		 Lot_Current_Status varchar(20)
 		 )
 
 AS
 begin
 	declare @results Table(Lot_ID int,
 		 		ProcessStep_ID bigint,
 		 		Location_ID int,
 		 		PS_Type_Name varchar(20),
 		 		Lot_Number varchar(50),
 		 		Lot_Current_Status varchar(20)
 		 		)
 	declare @temp int
 	declare @temp_process_step_id bigint
 	declare @temp_ps_type varchar(40)
 	declare @temp_ps_type_name varchar(40)
 	declare @temp_flow_cursor CURSOR
 
 	if @product_id = 0 and (@lot_number = null or @lot_number = '')
 	begin
 		--insert @results select lt.Lot_ID, lt.ProcessStep_ID, lt.Location_ID, ps.PS_Type_Name, ltrim(lt.Lot_Number), lt.Lot_Current_Status from Lot_Table lt, Process_Step ps where (lt.Lot_Current_Status='Eng Pull' or lt.Lot_Current_Status ='Running' or lt.Lot_Current_Status = 'Hold') and lt.ProcessStep_ID = ps.ProcessStep_ID  
		  insert @results select lt.Lot_ID, lt.ProcessStep_ID, lt.Location_ID, ps.PS_Type_Name, ltrim(lt.Lot_Number), lt.Lot_Current_Status from Lot_Table lt, Process_Step ps, Product p where (lt.Lot_Current_Status='Eng Pull' or lt.Lot_Current_Status ='Running' or lt.Lot_Current_Status = 'Hold') and lt.ProcessStep_ID = ps.ProcessStep_ID and ps.processstep_id = p.processstep_id and ( ( ps_type_name = 'SORT' AND p_type is null ) or ps_type_name <> 'SORT' ) and ps_type_name <> 'DIE BANK'
		
 	end
 	else if @product_id <> 0 and (@lot_number <> null or @lot_number <> '')
 	begin
 		set @temp_flow_cursor = CURSOR FOR
 		select *from fn_get_process_flow_by_product_id(@product_id)
 
 		open @temp_flow_cursor
 			fetch next from @temp_flow_cursor into @temp_process_step_id, @temp_ps_type, @temp_ps_type_name
 			while @@fetch_status = 0
 			begin
 			      --insert @results select lt.Lot_ID, lt.ProcessStep_ID, lt.Location_ID, ps.PS_Type_Name, ltrim(lt.Lot_Number), lt.Lot_Current_Status from Lot_Table lt, Process_Step ps where lt.ProcessStep_ID =@temp_process_step_id and lt.Lot_Number like @lot_number+'%' and (lt.Lot_Current_Status='Eng Pull' or lt.Lot_Current_Status ='Running' or lt.Lot_Current_Status = 'Hold') and lt.ProcessStep_ID = ps.ProcessStep_ID  
				insert @results select lt.Lot_ID, lt.ProcessStep_ID, lt.Location_ID, ps.PS_Type_Name, ltrim(lt.Lot_Number), lt.Lot_Current_Status from Lot_Table lt, Process_Step ps, Product p where lt.ProcessStep_ID =@temp_process_step_id and lt.Lot_Number like @lot_number+'%' and (lt.Lot_Current_Status='Eng Pull' or lt.Lot_Current_Status ='Running' or lt.Lot_Current_Status = 'Hold') and lt.ProcessStep_ID = ps.ProcessStep_ID and ps.processstep_id = p.processstep_id and ( ( ps_type_name = 'SORT' AND p_type is null ) or ps_type_name <> 'SORT' ) and ps_type_name <> 'DIE BANK'
 				fetch next from @temp_flow_cursor into @temp_process_step_id, @temp_ps_type, @temp_ps_type_name
 			end
 		close @temp_flow_cursor
 		deallocate @temp_flow_cursor
 		
 		
 	end
 	else if @product_id <> 0 and ( @lot_number = null or @lot_number = '')
 	begin
 		set @temp_flow_cursor = CURSOR FOR
 		select *from fn_get_process_flow_by_product_id(@product_id)
 
 		open @temp_flow_cursor
 			fetch next from @temp_flow_cursor into @temp_process_step_id, @temp_ps_type, @temp_ps_type_name
 			while @@fetch_status = 0
 			begin
 			      --insert @results select lt.Lot_ID, lt.ProcessStep_ID, lt.Location_ID, ps.PS_Type_Name, ltrim(lt.Lot_Number), lt.Lot_Current_Status from Lot_Table lt, Process_Step ps where lt.ProcessStep_ID =@temp_process_step_id and (lt.Lot_Current_Status='Eng Pull' or lt.Lot_Current_Status ='Running' or lt.Lot_Current_Status = 'Hold') and lt.ProcessStep_ID = ps.ProcessStep_ID  
 				insert @results select lt.Lot_ID, lt.ProcessStep_ID, lt.Location_ID, ps.PS_Type_Name, ltrim(lt.Lot_Number), lt.Lot_Current_Status from Lot_Table lt, Process_Step ps, Product p where lt.ProcessStep_ID =@temp_process_step_id and (lt.Lot_Current_Status='Eng Pull' or lt.Lot_Current_Status ='Running' or lt.Lot_Current_Status = 'Hold') and lt.ProcessStep_ID = ps.ProcessStep_ID  and ps.processstep_id = p.processstep_id and ( ( ps_type_name = 'SORT' AND p_type is null ) or ps_type_name <> 'SORT' ) and ps_type_name <> 'DIE BANK'
 				fetch next from @temp_flow_cursor into @temp_process_step_id, @temp_ps_type, @temp_ps_type_name
 			end
 		close @temp_flow_cursor
 		deallocate @temp_flow_cursor
 	
 	end
 	else
 	begin
 	      --insert @results select lt.Lot_ID, lt.ProcessStep_ID, lt.Location_ID, ps.PS_Type_Name, ltrim(lt.Lot_Number), lt.Lot_Current_Status from Lot_Table lt, Process_Step ps where lt.Lot_Number like @lot_number+'%' and (lt.Lot_Current_Status='Eng Pull' or lt.Lot_Current_Status ='Running' or lt.Lot_Current_Status = 'Hold') and lt.ProcessStep_ID = ps.ProcessStep_ID  
 		insert @results select lt.Lot_ID, lt.ProcessStep_ID, lt.Location_ID, ps.PS_Type_Name, ltrim(lt.Lot_Number), lt.Lot_Current_Status from Lot_Table lt, Process_Step ps, Product p where lt.Lot_Number like @lot_number+'%' and (lt.Lot_Current_Status='Eng Pull' or lt.Lot_Current_Status ='Running' or lt.Lot_Current_Status = 'Hold') and lt.ProcessStep_ID = ps.ProcessStep_ID  and ps.processstep_id = p.processstep_id and ( ( ps_type_name = 'SORT' AND p_type is null ) or ps_type_name <> 'SORT' ) and ps_type_name <> 'DIE BANK'
 	end
 
 	update @results set Lot_Current_Status = 'ACTIVE' where Lot_Current_Status = 'Running'
 
 	insert @fn_results select *from @results
 	return
 end
 
 



